﻿--	use master
go
	create database DoAnQuanLyBanHang
go
	use DoAnQuanLyBanHang
go
/* Đoạn code này dùng để drop database khi bị báo lỗi là database đang sữ dụng.
	USE master;
	GO
	ALTER DATABASE DoAnQuanLyBanHang 
	SET SINGLE_USER 
	WITH ROLLBACK IMMEDIATE;
	GO
	DROP DATABASE DoAnQuanLyBanHang;

*/

create table TrangThai
(
	MaTrangThai bit not null,
	TenTrangThai nvarchar(20),
	CONSTRAINT PK_TrangThai PRIMARY KEY (MaTrangThai)
)
go
create  table NguoiDung
(
	Ma int  IDENTITY (1,1) NOT NULL,
	MaNguoiDung AS 'ND' + RIGHT('0000' + CAST(Ma AS varCHAR(5)), 5) PERSISTED not null, 
	MaNhanVien	varchar(7),
	TaiKhoan	Nchar(20),
	MatKhau	Nchar(50),
	MaVaiTro	Varchar(7),
	DienGiai	ntext,
	MaTrangThai	bit,
	CONSTRAINT PK_NguoiDung PRIMARY KEY (MaNguoiDung)
)
go
create table ChucNang
(
	MaChucNang	Int not null,
	TenChucNang	Nvarchar(20),
	MaCha	Int,
	CONSTRAINT PK_ChucNang PRIMARY KEY (MaChucNang)
)
go
create table VaiTro
(
	Ma	int  IDENTITY (1,1) NOT NULL,
	MaVaiTro	AS 'VT' + RIGHT('0000' + CAST(Ma AS varCHAR(5)), 5) PERSISTED not null,
	TenVaiTro	Nvarchar(100),
	MaPhanQuyen	varchar(7),
	ChucNang	ntext,
	MaTrangThai	bit,
	CONSTRAINT PK_VaiTro PRIMARY KEY (MaVaiTro)
)
go
create table PhanQuyen
(
	Ma	int  IDENTITY (1,1) NOT NULL,
	MaPhanQuyen	AS 'PQ' + RIGHT('0000' + CAST(Ma AS varCHAR(5)), 5) PERSISTED not null,
	TenPhanQuyen	Nvarchar(100),
	CONSTRAINT PK_PhanQuyen PRIMARY KEY (MaPhanQuyen)
)
go
create table LinhVuc
(
	Ma	int  IDENTITY (1,1) NOT NULL,
	MaLinhVuc	AS 'LV' + RIGHT('0000' + CAST(Ma AS varCHAR(5)), 5) PERSISTED not null,
	TenLinhVuc	Nvarchar(100),
	CONSTRAINT PK_LinhVuc PRIMARY KEY (MaLinhVuc)
)
go
create table ThongTin
(
	Ma	int  IDENTITY (1,1) NOT NULL,
	TenDonVi	Ntext,
	DiaChi	Ntext,
	Fax	Ntext,
	DienThoai	Ntext,
	Website	Ntext,
	Email	Ntext,
	MaLinhVuc	varchar(7),
	MaSoThue	Ntext,
	GPKD	Ntext,
	CONSTRAINT PK_ThongTin PRIMARY KEY (Ma)
)
go
create table NhatKy
(
	Ma	int  IDENTITY (1,1) NOT NULL,
	MaNguoiDung	varchar(7),
	MayTinh	Ntext,
	ThoiGian	Datetime,
	ChucNang	Ntext,
	HanhDong	Ntext,
	DoiTuong	Ntext,
	CONSTRAINT PK_NhatKy PRIMARY KEY (Ma)
)
go
create table KhuVuc
(
	Ma	int  IDENTITY (1,1) NOT NULL,
	MaKhuVuc	AS 'KV' + RIGHT('0000' + CAST(Ma AS varCHAR(5)), 5) PERSISTED not null,
	TenKhuVuc	Nvarchar(100),
	GhiChu	Ntext,
	MaTrangThai	bit,
	CONSTRAINT PK_KhuVuc PRIMARY KEY (MaKhuVuc)
)
go
create table KhachHang
(
	Ma	int  IDENTITY (1,1) NOT NULL,
	MaKhachHang	AS 'KH' + RIGHT('0000' + CAST(Ma AS varCHAR(5)), 5) PERSISTED not null,
	TenKhachHang	Nvarchar(100),
	MaKhuVuc	Varchar(7),
	DiaChi	Ntext,
	MaSoThue	Ntext,
	DienThoai	Ntext,
	Email	Ntext,
	TaiKhoan	Ntext,
	Fax	Ntext,
	DiDong	Ntext,
	Website	Ntext,
	NganHang	Ntext,
	GioiHanNo	Float,
	ChiecKhau	Float,
	NoHienTai	Float,
	NickYahoo	Float,
	NickSkype	Float,
	NguoiLienHe	Nvarchar(150),
	MaTrangThai	bit,
	CONSTRAINT PK_KhachHang PRIMARY KEY (MaKhachHang)
)
------------------------------HUY BÌNH ----------------------------------------------------
go
create table NhaCungCap
(
	Ma int identity (1,1) not null,
	MaNhaCungCap as 'NCC' + right('0000' + cast(Ma as varchar(4)), 4) persisted,
	TenNhaCungCap nvarchar(100),
	MaKhuVuc varchar(7),
	DiaChi ntext,
	MaSoThue ntext,
	DienThoai ntext,
	Email ntext,
	TaiKhoan ntext,
	Fax	ntext,
	DiDong ntext,
	Website	ntext,
	NganHang ntext,
	GioiHanNo float,
	ChiecKhau float,
	NguoiLienHe	nvarchar(150),
	ChucVu nvarchar(50),
	MaTrangThai	bit,
	constraint PK_NhaCungCap primary key (MaNhaCungCap)
)
GO
create table NhomHang
(
	Ma int identity (1,1) not null,
	MaNhomHang as 'NH' + right('00000' + cast(Ma as varchar(5)), 5) persisted,
	TenNhomHang	nvarchar(100),
	GhiChu ntext,
	MaTrangThai	bit,
	constraint PK_NhomHang primary key (MaNhomHang)
)
GO
create table DonVi
(
	Ma int identity (1,1) not null,
	MaDonVi as 'DV' + right('00000' + cast(Ma as varchar(5)), 5) persisted,
	TenDonVi nvarchar(100),
	GhiChu ntext,
	constraint PK_DonVi primary key (MaDonVi)
)
GO
create table KhoHang
(
	Ma int identity (1,1) not null,
	MaKhoHang as 'KH' + right('00000' + cast(Ma as varchar(5)), 5) persisted,
	TenKhoHang nvarchar(100),
	MaNguoiQL varchar(7),
	NguoiLienHe	ntext,
	DiaChi ntext,
	DienThoai ntext,
	Fax	ntext,
	Email ntext,
	DienGia ntext,
	MaTrangThai	bit,
	constraint PK_KhoHang primary key (MaKhoHang)
)
GO
create table HangHoa
(
	Ma int identity (1,1) not null,
	MaHangHoa as 'HH' + right('00000' + cast(Ma as varchar(5)), 5) persisted,
	TenHangHoa nvarchar(100),
	MaNhaCungCap varchar(7),
	MaNhomHang varchar(7),
	MaLoaiHH int,
	MaDonVi	varchar(7),
	MaKhoHang varchar(7),
	XuatXu ntext,
	DiaChiAnh ntext,
	TonKhoToiThieu float,
	TonKhoHienTai float,
	GiaMua float,
	GiaBanLe float,
	GiaBanSi float,
	MaTrangThai	bit,
	constraint PK_HangHoa primary key (MaHangHoa)
)
GO
create table BoPhan
(
	Ma int identity (1,1) not null,
	MaBoPhan as 'BP' + right('00000' + cast(Ma as varchar(5)), 5) persisted,
	TenBoPhan nvarchar(100),
	GhiChu	Ntext,
	MaTrangThai	bit,
	constraint PK_BoPhan primary key (MaBoPhan)
)
GO
create table NhanVien
(
	Ma int identity (1,1) not null,
	MaNhanVien as 'NV' + right('00000' + cast(Ma as varchar(5)), 5) persisted,
	TenNhanVien	nvarchar(100),
	MaBoPhan varchar(7),
	MaNguoiQL varchar(7),
	MaTrangThai	bit,
	constraint PK_NhanVien primary key (MaNhanVien)
)
GO	
----------------------------------------Thanh------------------------------------------------------
go
create table TyGia
(
	Ma	int identity (1,1) not null,
	MaTyGia	as 'TG' + right('00000' + cast(Ma as varchar(5)), 5) persisted,
	TenTyGia	Nvarchar(100),
	TyGiaQuyDoi	Float,
	constraint PK_TyGia primary key (MaTyGia)
)
go
create table LoaiHangHoa
(
	Ma	int identity (1,1) not null,
	TenHangHoa	Nvarchar(100),
	constraint PK_LoaiHangHoa primary key (Ma)
)
go
create table PhieuGhiNhan
(
	MaPhieuGhiNhan	int identity (1,1) not null,
	MaLoaiPhieu	int,
	NgayLap	Datetime,
	TenPhieuGhiNhan	Nvarchar(50),
	MaNhanVienQL	varchar(7),
	constraint PK_PhieuGhiNhan primary key (MaPhieuGhiNhan)
)
go
create table LoaiPhieu
(
	MaLoaiPhieu int identity (1,1) not null,
	TenLoaiPhieu Nvarchar(30),
	constraint PK_LoaiPhieu primary key (MaLoaiPhieu)
)

go
create table PhieuMuaHang
(
	MaPhieuMuaHang	int identity (1,1) not null,
	TenPhieuMuaHang	Nvarchar(100),
	MaNhaCungCap	varchar(7),
	MaHangHoa	varchar(7),
	MaPhieuGhiNhan int,
	constraint PK_PhieuMuaHang primary key (MaPhieuMuaHang)
)
go
create table PhieuBanHang
(
	MaPhieuBanHang	int identity (1,1) not null,
	TenPhieuBanHang	Nvarchar(100),
	MaKhachHang	varchar(7),
	MaHangHoa	varchar(7),
	NgayGiao	Datetime,
	MaPhieuGhiNhan int,
	constraint PK_PhieuBanHang primary key (MaPhieuBanHang)
)
go
create table PhieuThu
(
	MaPhieuThu	int identity (1,1) not null,
	TenPhieuThu	Nvarchar(100),
	MaKhachHang	varchar(7),
	MaHangHoa	varchar(7),
	MaPhieuGhiNhan int,
	constraint PK_PhieuThu primary key (MaPhieuThu)
)
go
create table PhieuChi
(
	MaPhieuChi	int identity (1,1) not null,
	TenPhieuChi	Nvarchar(100),
	MaNhaCungCap	varchar(7),
	MaHangHoa	varchar(7),
	MaPhieuGhiNhan int,
	constraint PK_PhieuChi primary key (MaPhieuChi)
)
go
create table ChuyenKho
(
	MaPhieuChuyenKho	int identity (1,1) not null,
	TenPhieuChuyenKho	Nvarchar(100),
	MaNhanVien	varchar(7),
	MaHangHoa	varchar(7),
	MaKhoHang varchar(7),
	constraint PK_ChuyenKho primary key (MaPhieuChuyenKho)
)
go
create table TonKho
(
	Ma	int identity (1,1) not null,
	TenTonKho	Nvarchar(100),
	MaKhoHang	varchar(7),
	MaHangHoa	varchar(7),
	constraint PK_TonKho primary key (Ma)
)
/*
-------------------------------------------------------------------------------------------------------------------
										KHÓA NGOẠI CÁC BẢNG
-------------------------------------------------------------------------------------------------------------------	
*/
go
alter table NguoiDung add constraint fk_NguoiDung_TrangThai  foreign key (MaTrangThai) references TrangThai(MaTrangThai)
go
alter table VaiTro add constraint fk_VaiTro_TrangThai  foreign key (MaTrangThai) references TrangThai(MaTrangThai)
go
alter table KhuVuc add constraint fk_KhuVuc_TrangThai  foreign key (MaTrangThai) references TrangThai(MaTrangThai)
go
alter table KhachHang add constraint fk_KhachHang_TrangThai  foreign key (MaTrangThai) references TrangThai(MaTrangThai)
go
alter table NhaCungCap add constraint fk_NhaCungCap_TrangThai  foreign key (MaTrangThai) references TrangThai(MaTrangThai)
go
alter table NhomHang add constraint fk_NhomHang_TrangThai  foreign key (MaTrangThai) references TrangThai(MaTrangThai)
go
alter table KhoHang add constraint fk_KhoHang_TrangThai  foreign key (MaTrangThai) references TrangThai(MaTrangThai)
go
alter table HangHoa add constraint fk_HangHoa_TrangThai  foreign key (MaTrangThai) references TrangThai(MaTrangThai)
go
alter table BoPhan add constraint fk_BoPhan_TrangThai  foreign key (MaTrangThai) references TrangThai(MaTrangThai)
go
alter table NhanVien add constraint fk_NhanVien_TrangThai  foreign key (MaTrangThai) references TrangThai(MaTrangThai)
go


alter table NguoiDung add constraint fk_NguoiDung_NhanVien  foreign key (MaNhanVien) references NhanVien(MaNhanVien)
go
alter table NguoiDung add constraint fk_NguoiDung_VaiTro  foreign key (MaVaiTro) references VaiTro(MaVaiTro)
go
alter table VaiTro add constraint fk_VaiTro_PhanQuyen  foreign key (MaPhanQuyen) references PhanQuyen(MaPhanQuyen)
go
alter table ThongTin add constraint fk_ThongTin_LinhVuc  foreign key (MaLinhVuc) references LinhVuc(MaLinhVuc)
go
alter table NhatKy add constraint fk_NhatKy_NguoiDung  foreign key (MaNguoiDung) references NguoiDung(MaNguoiDung)
go
alter table KhachHang add constraint fk_KhachHang_KhuVuc  foreign key (MaKhuVuc) references KhuVuc(MaKhuVuc)
------------------------------------------------HUY BÌNH------------------------------------------------------
--Tạo khóa ngoại bảng NhaCungCap
go
alter table NhaCungCap 
add constraint FK_NhaCungCap_KhuVuc foreign key (MaKhuVuc) references KhuVuc(MaKhuVuc)
GO
--Tạo khóa ngoại bảng KhoHang
alter table KhoHang 
add constraint FK_KhoHang_NhanVien foreign key (MaNguoiQL) references NhanVien(MaNhanVien)
GO
--Tạo khóa ngoại bảng HangHoa
alter table HangHoa 
add constraint FK_HangHoa_NhaCungCap foreign key (MaNhaCungCap) references NhaCungCap(MaNhaCungCap)
GO
alter table HangHoa 
add constraint FK_HangHoa_NhomHang foreign key (MaNhomHang) references NhomHang(MaNhomHang)
GO
alter table HangHoa 
add constraint FK_HangHoa_LoaiHangHoa foreign key (MaLoaiHH) references LoaiHangHoa(Ma)
GO
alter table HangHoa 
add constraint FK_HangHoa_DonVi foreign key (MaDonVi) references DonVi(MaDonVi)
GO
alter table HangHoa 
add constraint FK_HangHoa_KhoHang foreign key (MaKhoHang) references KhoHang(MaKhoHang)
GO
--Tạo khóa ngoại bảng NhanVien
alter table NhanVien 
add constraint FK_NhanVien_BoPhan foreign key (MaBoPhan) references BoPhan(MaBoPhan)
GO
alter table NhanVien 
add constraint FK_NhanVien_NhanVien foreign key (MaNguoiQL) references NhanVien(MaNhanVien)
go

alter table PhieuGhiNhan add constraint fk_PhieuGhiNhan_NhanVien  foreign key (MaNhanVienQL) references NhanVien(MaNhanVien)
go
alter table PhieuGhiNhan add constraint fk_PhieuGhiNhan_LoaiPhieu  foreign key (MaLoaiPhieu) references LoaiPhieu(MaLoaiPhieu)
go

alter table PhieuMuaHang add constraint fk_PhieuMuaHang_NhaCungCap  foreign key (MaNhaCungCap) references NhaCungCap(MaNhaCungCap)
go
alter table PhieuMuaHang add constraint fk_PhieuMuaHang_HangHoa  foreign key (MaHangHoa) references HangHoa(MaHangHoa)
go
alter table PhieuMuaHang add constraint fk_PhieuMuaHang_PhieuGhiNhan  foreign key (MaPhieuGhiNhan) references PhieuGhiNhan(MaPhieuGhiNhan)
go

alter table PhieuBanHang add constraint fk_PhieuBanHang_HangHoa  foreign key (MaHangHoa) references HangHoa(MaHangHoa)
go
alter table PhieuBanHang add constraint fk_PhieuBanHang_KhachHang  foreign key (MaKhachHang) references KhachHang(MaKhachHang)
go
alter table PhieuBanHang add constraint fk_PhieuBanHang_PhieuGhiNhan  foreign key (MaPhieuGhiNhan) references PhieuGhiNhan(MaPhieuGhiNhan)
go

alter table PhieuThu add constraint fk_PhieuThu_KhachHang  foreign key (MaKhachHang) references KhachHang(MaKhachHang)
go
alter table PhieuThu add constraint fk_PhieuThu_HangHoa  foreign key (MaHangHoa) references HangHoa(MaHangHoa)
go
alter table PhieuThu add constraint fk_PhieuThu_PhieuGhiNhan  foreign key (MaPhieuGhiNhan) references PhieuGhiNhan(MaPhieuGhiNhan)
go

alter table PhieuChi add constraint fk_PhieuChi_NhaCungCap  foreign key (MaNhaCungCap) references NhaCungCap(MaNhaCungCap)
go
alter table PhieuChi add constraint fk_PhieuChi_HangHoa  foreign key (MaHangHoa) references HangHoa(MaHangHoa)
go
alter table PhieuChi add constraint fk_PhieuChi_PhieuGhiNhan  foreign key (MaPhieuGhiNhan) references PhieuGhiNhan(MaPhieuGhiNhan)
go

alter table ChuyenKho add constraint fk_ChuyenKho_NhanVien  foreign key (MaNhanVien) references NhanVien(MaNhanVien)
go
alter table ChuyenKho add constraint fk_ChuyenKho_HangHoa  foreign key (MaHangHoa) references HangHoa(MaHangHoa)
go
alter table ChuyenKho add constraint fk_ChuyenKho_KhoHang foreign key (MaKhoHang) references KhoHang(MaKhoHang)
go
alter table TonKho add constraint fk_TonKho_HangHoa  foreign key (MaHangHoa) references HangHoa(MaHangHoa)
go
alter table TonKho add constraint fk_TonKho_KhoHang  foreign key (MaKhoHang) references KhoHang(MaKhoHang)
go
/*
-------------------------------------------------------------------------------------------------------------------
										RÀNG BUỘC DỮ LIỆU
-------------------------------------------------------------------------------------------------------------------	
*/
alter table NguoiDung add constraint  U_TaiKhoan Unique(TaiKhoan)

/*
-------------------------------------------------------------------------------------------------------------------
										INSERT DỮ LIỆU
-------------------------------------------------------------------------------------------------------------------	
*/
--cây hệ thống
insert into ChucNang values (1, N'Hệ Thống', 0)
insert into ChucNang values (2, N'Hệ Thống', 1)
insert into ChucNang values (3, N'Đơn Vị', 2)
insert into ChucNang values (4, N'Bảo Mật', 1)
insert into ChucNang values (5, N'Quản Lý Phân Quyền', 4)
insert into ChucNang values (6, N'Quản Lý Người Dùng', 5)
insert into ChucNang values (7, N'Vai Trò & Quyền Hạn', 5)
insert into ChucNang values (8, N'Đổi Mật Khẩu', 4)
insert into ChucNang values (9, N'Nhật Kí Hệ Thống', 4)
insert into ChucNang values (10, N'Dữ Liệu', 1)
insert into ChucNang values (11, N'Sao Lưu', 10)
insert into ChucNang values (12, N'Phục Hồi', 10)
insert into ChucNang values (13, N'Sửa Chữa', 10)
insert into ChucNang values (14, N'Kết Chuyển', 10)
--cây danh mục
insert into ChucNang values (15, N'Danh Mục', 0)
insert into ChucNang values (16, N'Đối Tác', 15)
insert into ChucNang values (17, N'Khu Vực', 16)
insert into ChucNang values (18, N'Khách Hàng', 16)
insert into ChucNang values (19, N'Nhà Phân Phối', 16)
insert into ChucNang values (20, N'Kho Hàng', 15)
insert into ChucNang values (21, N'Kho', 20)
insert into ChucNang values (22, N'Đon Vị', 20)
insert into ChucNang values (23, N'Nhóm Hàng', 20)
insert into ChucNang values (24, N'Hàng Hóa', 20)
insert into ChucNang values (25, N'In Mã Vạch', 20)
insert into ChucNang values (26, N'Tỷ Giá', 20)
insert into ChucNang values (27, N'Bộ Phận', 15)
insert into ChucNang values (28, N'Bộ Phận', 15)
insert into ChucNang values (29, N'Nhân Viên', 15)
--cây chức năng
insert into ChucNang values (30, N'Chức Năng', 0)
insert into ChucNang values (31, N'Bán Hàng', 30)
insert into ChucNang values (32, N'Mua Hàng', 31)
insert into ChucNang values (33, N'Bán Hàng', 31)
insert into ChucNang values (34, N'Công Nợ', 30)
insert into ChucNang values (35, N'Thu Tiền', 34)
insert into ChucNang values (36, N'Trả Tiền', 34)
insert into ChucNang values (37, N'Kho Hàng', 30)
insert into ChucNang values (38, N'Nhập Kho', 37)
insert into ChucNang values (39, N'Xuất Kho', 37)
insert into ChucNang values (40, N'Tồn Kho', 37)
insert into ChucNang values (41, N'Tiện Ích', 30)
insert into ChucNang values (42, N'Đóng Gói', 41)
insert into ChucNang values (43, N'Kiểm Kê', 41)
insert into ChucNang values (44, N'Chuyển Kho', 41)
insert into ChucNang values (45, N'Tổng Hợp Tồn Kho', 41)
insert into ChucNang values (46, N'Hóa Đơn', 30)
insert into ChucNang values (47, N'Hóa Đơn', 46)
insert into ChucNang values (48, N'Quản Lý Chứng Từ', 46)
insert into ChucNang values (49, N'Báo Cáo', 30)
insert into ChucNang values (50, N'Báo Cáo Kho', 49)
insert into ChucNang values (51, N'Doanh Thu Bán Hàng', 49)
insert into ChucNang values (52, N'Hạn Sử Dụng', 49)
insert into ChucNang values (53, N'Công Cụ', 30)
insert into ChucNang values (54, N'Đặt Hàng', 53)
insert into ChucNang values (55, N'Nhập Số Dư Ban Đầu', 53)
insert into ChucNang values (56, N'Lịch Sử Hàng Hóa', 53)

--------------------------------------------Nguoi dung-------------------------------
INSERT [dbo].[NguoiDung] ([TaiKhoan], [MatKhau], [MaVaiTro], [DienGiai], [MaTrangThai]) 
VALUES (N'thanh', N'thanh', NULL, N'sds', NULL)

go
--@TanMy
--Store ThemKhuVuc
create proc ThemKhuVuc @TenKV nvarchar(100), @GhiChu ntext ,@MaTT int
as 
begin
	insert into KhuVuc (TenKhuVuc, GhiChu, MaTrangThai) values(@TenKV, @GhiChu, @MaTT)
end
go

-- lấy tên sql sever 
SELECT name FROM sys.servers WHERE SERVER_id=0
-- lấy tên database
SELECT DB_NAME() AS [Current Database];
----------------------------------------------Hàm Lấy DS Nhật Ký------------------------------------
go
create proc usp_layDsNhatKy
as
begin
	select * from NhatKy
end
go
----------------------------------------------Hàm insert Nhật Ký------------------------------------
go
create proc usp_themNhatKy @MaNguoiDung varchar(7), @MayTinh ntext, @ThoiGian datetime, @ChucNang ntext, @HanhDong ntext, @DoiTuong ntext
as
begin
	insert into NhatKy (MaNguoiDung,MayTinh,ThoiGian,ChucNang,HanhDong,DoiTuong) values
	(@MaNguoiDung, @MayTinh, @ThoiGian, @ChucNang, @HanhDong, @DoiTuong)
end
go
exec usp_themNhatKy 'ND00001', 'THanhBui-PC', '2014-12-23',N'Hệ Thống', 'Xem',''
go